﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using TruckScale.Helpers;
using static TruckScale.Helpers.LogHelper;

namespace TruckScale.Models
{
  public class SpareModel
  {
    public int? AutoNo { get; set; }
    public string SpareName { get; set; }
    public string SpareKey { get; set; }
    public string SpareValue { get; set; }

    public static List<SpareModel> SelectSpare()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          //string sql = "select * from spare;";
          string sql = "SELECT spare.auto_no, spare.spare_key, spare.spare_value, weighform.weighform_value as spare_name FROM spare JOIN weighform ON spare.spare_key = weighform.weighform_key;";
          IEnumerable<SpareModel> output = cnn.Query<SpareModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<SpareModel> SelectSpareByKey(string key)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from spare where spare_key = '{key}' and spare_value not like '=%';";
          IEnumerable<SpareModel> output = cnn.Query<SpareModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static List<SpareModel> SelectSpareField()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from spare where spare_value like '=%';";
          IEnumerable<SpareModel> output = cnn.Query<SpareModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }

    public static int SelectSpareField(string key)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select count(*) from spare where spare_key = '{key}' and spare_value like '=%';";
          int output = cnn.Query<int>(sql).FirstOrDefault();
          return output;
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }

    public static int InsertSpare(SpareModel spare)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "insert into spare (spare_key, spare_value) values (@SpareKey, @SpareValue);";
          return cnn.Execute(sql, spare);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int UpdateSpare(SpareModel spare)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "UPDATE spare SET spare_key = @SpareKey, spare_value = @SpareValue WHERE auto_no = @AutoNo;";
          return cnn.Execute(sql, spare);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int DeleteSpare(SpareModel spare)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"delete from spare where auto_no = '{spare.AutoNo}'";
          return cnn.Execute(sql);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
  }
}
